#include "goodsqlhelp.h"
#include "commonhelper.h"
#include "tableInfo.h"
#include <QSqlQuery>
#include <QSqlRecord>


GoodSqlHelp::GoodSqlHelp(QObject *parent) : QObject(parent)
{

}

bool GoodSqlHelp::queryGoods(const QString goodName, QStringList &headList, QList<QStringList> &dataList)
{
    QSqlQuery query;
    QString sql = QString("select * FROM %1 AS g").arg(g_goodsTableName)
            + QString(" LEFT OUTER JOIN %1 AS c ON g.%2 = c.%3").arg(g_categorieTableName).arg(g_goodsColumn[Good_Categories]).arg(g_CategorieColumn[Categorie_Id])
            + QString(" LEFT OUTER JOIN %1 as u ON g.%2 = u.%3 ").arg(g_unitTableName).arg(g_goodsColumn[Good_Units]).arg(g_unitColumn[Unit_Id])
            + QString(" LEFT OUTER JOIN %1 as b On g.%2 = b.%3").arg(g_barCodeTableName).arg(g_goodsColumn[Good_Id]).arg(g_barCodeColumn[BarCode_GoodId]);
    if(goodName != "") {
        sql += QString(" where g.%1 like '\%%7\%' or g.%2 like '\%%7\%' or g.%3 like '\%%7\%' or c.%4 like '\%%7\%' or u.%5 like '\%%7\%'"
                        " or b.%6 like '\%%7\%'")
                .arg(g_goodsColumn[Good_FullName]).arg(g_goodsColumn[Good_SimpleName])
                .arg(g_goodsColumn[Good_Id]).arg(g_CategorieColumn[Categorie_Name]).arg(g_unitColumn[Unit_Name])
                .arg(g_barCodeColumn[BarCode_Code]).arg(goodName);

    }
    sql += QString(" order by g.%1 ").arg(g_goodsColumn[Good_Id]);
    //xdebug<<sql;
    if(!query.exec(sql)) //执行失败
        return  false;
    while (query.next())
    {
        QStringList list;
        list << query.value(g_goodsColumn[Good_Id]).toString()
            << query.value(g_barCodeColumn[BarCode_Code]).toString()
            << query.value(g_goodsColumn[Good_FullName]).toString()
            << query.value(g_goodsColumn[Good_SimpleName]).toString()
            << query.value(g_goodsColumn[Good_Count]).toString()
            << query.value(g_unitColumn[Unit_Name]).toString()
            << query.value(g_CategorieColumn[Categorie_Name]).toString()
            << query.value(g_goodsColumn[Good_SellPrice]).toString()
            << query.value(g_goodsColumn[Good_EnterPrice]).toString()
            << query.value(g_goodsColumn[Good_AddTime]).toString();
        dataList.append(list);
    }
    return true;
}
//删除一个商品记录
bool GoodSqlHelp::removeGoodDate(const QString goodId, const QString barCode)
{
    if(goodId == "" || barCode == "")
        return false;
    QSqlQuery query;
    //先查找有几个编号为goodid的记录
    QString sql = QString("select count('%2') as count from %1 where %2 = '%3'").arg(g_barCodeTableName).arg(g_barCodeColumn[BarCode_GoodId]).arg(goodId);
    if(!query.exec(sql))
        return false;
    query.next();
    int size = query.value("count").toInt();
    // xdebug<<size;
    //先删除barcode表的数据，若此编号只有1个记录则删除goods表数据
    sql = QString("delete from %1 ").arg(g_barCodeTableName)
        + QString(" where %1 = '%2' ").arg(g_barCodeColumn[BarCode_GoodId]).arg(goodId)
        + QString(" and %1 = '%2'").arg(g_barCodeColumn[BarCode_Code]).arg(barCode);
    //xdebug<<sql;
    if(!query.exec(sql)) //执行失败
         return  false;
    if(size == 1) {
        sql = QString("delete from %1").arg(g_goodsTableName)
            + QString(" where %1 = '%2'").arg(g_goodsColumn[Good_Id]).arg(goodId);
        xdebug << sql ;
        if(!query.exec(sql)) //执行失败
            return  false;
    }
    return true;
}


/*
 *函数：BindDataToCombox()
 *输入：数据表名，数据表中要绑定的列的名称，绑定的数据，所要绑定的控件名称
 *返回值：无
*/
void GoodSqlHelp::BindDataToCombox(const QString &tableName, const QString &tableColumnName,
                                   const QString &userDataField, QComboBox *cbox)
{
    QSqlQuery query;
    query.exec("SELECT "+tableColumnName+","+userDataField+" FROM "+tableName+" ORDER BY "+tableColumnName+" DESC");
    while(query.next())
    {
        cbox->addItem(query.value(0).toString(), query.value(1).toInt());
    }
}

//查找编号是否已存在
bool GoodSqlHelp::istGoodIdExist(const QString &goodId, QString &Info)
{
    QString sql = QString("select * from %1 where %2 = '%3'").arg(g_goodsTableName).arg(g_goodsColumn[Good_Id]).arg(goodId);
    //xdebug << sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        Info = "数据库语句执行失败";
        return true;
    }
    if(query.next())
    {
        if (query.isValid()) {
            Info = "此编号已经存在";
            return true;
        }
    }
    return false;
}

//增加一条商品信息和条码
bool GoodSqlHelp::addGood(QStringList &infoList, const QString &goodBarCode)
{
    //添加goods表
    QSqlDatabase db = QSqlDatabase::database();
    if(!db.transaction())
        return false;
     QString sql = QString("insert into %1 values ('%2', '%3', '%4', %5, %6, %7, %8, %9, '%10','','')")
              .arg(g_goodsTableName).arg(infoList[Good_Id])
             .arg(infoList[Good_FullName]).arg(infoList[Good_SimpleName])
             .arg(infoList[Good_Count]).arg(infoList[Good_Units])
             .arg(infoList[Good_Categories]).arg(infoList[Good_SellPrice])
             .arg(infoList[Good_EnterPrice]).arg(infoList[Good_AddTime]);
     xdebug << sql;
     QSqlQuery query;
     if(!query.exec(sql)) {
         db.rollback();
         return false;
     }
     //添加条码表
     QStringList barCodeList = goodBarCode.split(";");
     foreach (const QString &code, barCodeList) {
         sql = QString("insert into %1 ").arg(g_barCodeTableName);
         sql += QString(" values('%1', '%2', '')").arg(infoList[Good_Id]).arg(code);
         xdebug << sql;
         if(!query.exec(sql)) {
             db.rollback();
             return false;
         }
     }
     db.commit();
     return true;
}

bool GoodSqlHelp::updateGood(QStringList &infoList,const QString goodBarCode)
{
    xdebug << infoList << goodBarCode;
    //更新goods表
    QSqlDatabase db = QSqlDatabase::database();
    if(!db.transaction())
        return false;
     QString sql = QString("update %1 set %2 = '%3', ").arg(g_goodsTableName).arg(g_goodsColumn[Good_FullName]).arg(infoList[Good_FullName])
              + QString(" %1 = '%2', ").arg(g_goodsColumn[Good_SimpleName]).arg(infoList[Good_SimpleName])
              + QString(" %1 = %2, ").arg(g_goodsColumn[Good_Count]).arg(infoList[Good_Count])
              + QString(" %1 = %2, ").arg(g_goodsColumn[Good_Units]).arg(infoList[Good_Units])
              + QString(" %1 = %2, ").arg(g_goodsColumn[Good_Categories]).arg(infoList[Good_Categories])
              + QString(" %1 = %2, ").arg(g_goodsColumn[Good_SellPrice]).arg(infoList[Good_SellPrice])
              + QString(" %1 = %2, ").arg(g_goodsColumn[Good_EnterPrice]).arg(infoList[Good_EnterPrice])
              + QString(" %1 = '%2' ").arg(g_goodsColumn[Good_AddTime]).arg(infoList[Good_AddTime])
              + QString( "where %1 = '%2'" ).arg(g_goodsColumn[Good_Id]).arg(infoList[Good_Id]);
     //xdebug << sql;
     QSqlQuery query;
     if(!query.exec(sql)) {
         db.rollback();
         return false;
     }
     //更新条码表
     sql = QString("update %1 set %2 = '%3' where %4 = '%5' and %6 = %7").arg(g_barCodeTableName).arg(g_barCodeColumn[BarCode_Code])
             .arg(goodBarCode).arg(g_barCodeColumn[BarCode_GoodId]).arg(infoList[Good_Id])
             .arg(g_barCodeColumn[BarCode_Code]).arg(goodBarCode);
     //xdebug << sql;
     if(!query.exec(sql)) {
         db.rollback();
         return false;
     }
     db.commit();
     return true;
}

//增加新单位
bool GoodSqlHelp::addUnit(QString newUnit)
{
    //xdebug<<newUnit;
    QString sql = QString("insert into %1(%2) values('%3')").arg(g_unitTableName).arg(g_unitColumn[Unit_Name]).arg(newUnit);
    QSqlQuery query;
    if(!query.exec(sql)) {
        return false;
    }
    return true;
}

//增加新种类
bool GoodSqlHelp::addCategorie(QString newCategorie)
{
    //xdebug<<newCategorie;
    QString sql = QString("insert into %1(%2) values('%3')").arg(g_categorieTableName).arg(g_CategorieColumn[Categorie_Name]).arg(newCategorie);
    QSqlQuery query;
    if(!query.exec(sql)) {
        return false;
    }
    return true;
}

//单位是否存在
bool GoodSqlHelp::isUnitExist(QString unitName)
{
    QString sql = QString("select * from %1 where %2 = '%3'").arg(g_unitTableName).arg(g_unitColumn[Unit_Name]).arg(unitName.trimmed());
    //xdebug << sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        return true;
    }
    if(query.next())
    {
        if (query.isValid()) {
            return true;
        }
    }
    return false;
}

bool GoodSqlHelp::isCategorieExist(QString categorieName)
{
    QString sql = QString("select * from %1 where %2 = '%3'").arg(g_categorieTableName).arg(g_CategorieColumn[Categorie_Name])
                        .arg(categorieName.trimmed());
    //xdebug << sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        return true;
    }
    if(query.next())
    {
        if (query.isValid()) {
            return true;
        }
    }
    return false;
}

int GoodSqlHelp::selectUnitNum(QString unitName)
{
    QString sql = QString("select * from %1 where %2 = '%3'").arg(g_unitTableName).arg(g_unitColumn[Unit_Name]).arg(unitName.trimmed());
    //xdebug << sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        return true;
    }
    if(query.next())
    {
        if (query.isValid()) {
            return query.value(0).toInt();
        }
    }
    return -1;
}

int GoodSqlHelp::selectCategorieNum(QString categorieName)
{
    QString sql = QString("select * from %1 where %2 = '%3'").arg(g_categorieTableName).arg(g_CategorieColumn[Categorie_Name])
                        .arg(categorieName.trimmed());
    //xdebug << sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        return true;
    }
    if(query.next())
    {
        if (query.isValid()) {
            return query.value(0).toInt();
        }
    }
    return -1;
}


//删除指定id的单位
bool GoodSqlHelp::deleteUnit(int unitId)
{
    //xdebug << unitId;
    QString sql = QString("delete from %1 where %2 = %3").arg(g_unitTableName).arg(g_unitColumn[Unit_Id]).arg(unitId);
    QSqlQuery query;
    if(!query.exec(sql)) {
        return false;
    }
    return true;
}

//删除指定id的种类
bool GoodSqlHelp::deleteCategorie(int categorieId)
{
     xdebug << categorieId;
     QString sql = QString("delete from %1 where %2 = %3").arg(g_categorieTableName).arg(g_CategorieColumn[Categorie_Id]).arg(categorieId);
     QSqlQuery query;
     if(!query.exec(sql)) {
         return false;
     }
     return true;
}

bool GoodSqlHelp::addBarCode(const QString goodId, QString barCode, QString &errorString)
{
    QString sql = QString("select * from %1 where %2 = '%3' and  %4 = '%5'")
            .arg(g_barCodeTableName).arg(g_barCodeColumn[BarCode_GoodId]).arg(goodId)
            .arg(g_barCodeColumn[BarCode_Code]).arg(barCode);
    xdebug<<sql;
    QSqlQuery query;
    if(!query.exec(sql)) {
        errorString = "增加失败001！";
        return false;
    }
    if(query.next())
    {
        if (query.isValid()) {
            errorString = "此条码已经存在";
            return false;
        }
    }

    sql = QString("insert into %1 values ('%2', '%3', '')").arg(g_barCodeTableName).arg(goodId).arg(barCode);
    //xdebug<<sql;
    if(!query.exec(sql)) {
        errorString = "增加失败002！";
        return false;
    }
    return true;
}


